USE SAS
GO


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'SearchActivities')
	BEGIN
		DROP  Procedure  dbo.SearchActivities
	END

GO

CREATE Procedure dbo.SearchActivities
	@text		VARCHAR(max)
,	@userId		INT
AS
BEGIN

	DECLARE 
		@R_OWNER	INT

	SELECT
		@R_OWNER	= 3

	;WITH A AS (
		SELECT 
			a.ActivityTaskID	AS ActivityID
		,	a.ActivityTaskGUID	AS PKID
		,	a.[Description]		AS ActivityName
		,	a.StartDate			as StartDate
		,	s.StatusDesc		as ActivityStatus
		,	t.TypeDesc			AS ActivityType
		FROM TR_ACTIVITYTASK as a WITH (NOLOCK)
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			on r.SourceGUID = a.ActivityTaskGUID
		LEFT OUTER JOIN TR_STATUS as S WITH (NOLOCK)
			ON s.Status = a.Status
		LEFT OUTER JOIN TR_ACTIVITYTYPE as T WITH (NOLOCK)
			ON t.ActivityType = a.ActivityType
		WHERE 
			r.Partner2 = @userID
		AND r.RelationShipType = @R_OWNER -- OWNER
		AND	ISNULL(a.IsTask,0) = 0
		AND [Description] LIKE @text

		UNION
		-- SEARCH IN NOTES
		SELECT 
			a.ActivityTaskID	AS ActivityID
		,	a.ActivityTaskGUID	AS PKID
		,	a.[Description]		AS ActivityName
		,	a.StartDate			as StartDate
		,	s.StatusDesc		as ActivityStatus
		,	t.TypeDesc			AS ActivityType
		FROM TR_ACTIVITYTASK as a WITH (NOLOCK)
		INNER JOIN TR_NOTE AS N WITH (NOLOCK)
			ON N.SourceGUID = A.ActivityTaskGUID
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			on r.SourceGUID = a.ActivityTaskGUID
		LEFT OUTER JOIN TR_STATUS as S WITH (NOLOCK)
			ON s.Status = a.Status
		LEFT OUTER JOIN TR_ACTIVITYTYPE as T WITH (NOLOCK)
			ON t.ActivityType = a.ActivityType
		WHERE 
			r.Partner2 = @userID
		AND r.RelationShipType = @R_OWNER -- OWNER
		AND	ISNULL(a.IsTask,0) = 0
		AND	N.NoteText LIKE @text
	), B AS (
		-- GET ACCOUNTS FOR THE ACTIVITIES
		SELECT
			act.PartnerName		as 	AccountName
		,	a.ActivityID
		FROM A 
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.SourceGUID = a.PKID
		INNER JOIN TR_PARTNER as act WITH (NOLOCK)
			on act.PartnerNo = r.Partner2
		WHERE
			r.RelationShiptype = 9 -- 9: Activity Partner
	), C AS (
		-- GET CONTACT FOR THE ACTIVITIES
		SELECT
			(con.PartnerFirstName + ' ' + con.PartnerLastName) as  ContactName
		,	a.ActivityID
		FROM A 
		INNER JOIN TR_RELATIONSHIPS as r WITH (NOLOCK)
			ON r.SourceGUID = a.PKID
		INNER JOIN TR_PARTNER as con WITH (NOLOCK)
			on con.PartnerNo = r.Partner2
		WHERE
			r.RelationShiptype = 2 -- 2: Contact
			AND r.IsMain = 1
	), Final AS (
		SELECT
			a.ActivityID		AS [Activity ID]
		,	a.ActivityName		AS [Activity Name]
		,	b.AccountName		AS [Account Name]
		,	a.ActivityType		as [Activity Type]
		,	a.ActivityStatus	as [Status]
		,	a.StartDate			as [Start Date]
		,	c.ContactName		as [Contact]
		,	a.PKID				AS [PKID]
		FROM A
		LEFT OUTER JOIN B
			on B.ActivityID = A.ActivityID
		LEFT OUTER JOIN C
			on C.ActivityID = A.ActivityID
	)

	SELECT * FROM Final
END
GO

-- exec SearchActivities

-- select * from tr_partner order by 1
